﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;

using XMK.Framework.DataAccess;
using XMK.Tadong.Model;
using XMK.Utils;
using XMK.Framework.Model;
using XMK.Tadong.Model.Cond;

namespace XMK.Tadong.Data
{
    public class ZnkzData
    {
        protected DbProvider dbProvider;
        public ZnkzData(DbProvider dbProvider)
        {
            this.dbProvider = dbProvider;
        }
        /// <summary>
        /// 获取主题分类
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable getUserSubject(string userId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"select a.subj_id,a.subj_name,a.keyw,a.ext_cont,a.prority,a.match,a.where_fmt from tb_subject a join tb_themetype_theme b on a.subj_id=b.subj_id where b.subj_class_id in(select a.subj_class_id from tb_subject_class a join tb_user_theme_type b on a.subj_class_id=b.subj_class_id where b.user_id='" + userId + "') order by a.prority ");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Copy();
            }
            return null;
    
        }

        /// <summary>
        /// 根据查询语句搜索扩展词
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable getExpanWordsBySql(string sqlStr)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(sqlStr);
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable ds = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            if (ds != null)
            {
                return ds.Copy();
            }
            return null;

        }

        /// <summary>
        /// 主题列表
        /// </summary>
        /// <returns></returns>
        public List<Tb_SubjectInfo> QuerySubjectList(PageInfo pageInfo, OrderInfo order)
        {
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, "sp_getpageSubjectList",
                   delegate()
                   {
                       return DbCmdHelper.CreateParameters(dbProvider, true).ToArray();
                   });
            cmd.SetParamValues(order, pageInfo);
            List<Tb_SubjectInfo> exttypeList = DbManager.GetPageInfos<Tb_SubjectInfo>(cmd, pageInfo,
                delegate(out Tb_SubjectInfo dict, DataRow row)
                {
                    dict = new Tb_SubjectInfo
                    {
                        SUBJ_ID = ParseHelper.Parse<string>(row["SUBJ_ID"]),
                        SUBJ_NAME = ParseHelper.Parse<string>(row["SUBJ_NAME"]),
                        KEYW = ParseHelper.Parse<string>(row["KEYW"]),
                        EXT_CONT = ParseHelper.Parse<string>(row["EXT_CONT"]),
                        PRORITY = ParseHelper.Parse<decimal>(row["PRORITY"]),
                        MATCH = ParseHelper.Parse<decimal>(row["MATCH"]),
                        WHERE_FMT = ParseHelper.Parse<string>(row["WHERE_FMT"]),
                        SUBJ_CLASS_NAME = ParseHelper.Parse<string>(row["SUBJ_CLASS_NAME"])

                    };
                });
            return exttypeList;
        }
        /// <summary>
        /// 根据id获取主题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Tb_SubjectInfo getSubjectById(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from TB_SUBJECT where SUBJ_ID =" + id + "");

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            Tb_SubjectInfo Obj = DbManager.GetInfo(cmd,
                delegate(out Tb_SubjectInfo u, DataRow row)
                {
                    u = new Tb_SubjectInfo
                    {
                        SUBJ_ID = ParseHelper.Parse<string>(row["SUBJ_ID"]),
                        SUBJ_NAME = ParseHelper.Parse<string>(row["SUBJ_NAME"]),
                        KEYW = ParseHelper.Parse<string>(row["KEYW"]),
                        EXT_CONT = ParseHelper.Parse<string>(row["EXT_CONT"]),
                        PRORITY = ParseHelper.Parse<decimal>(row["PRORITY"]),
                        MATCH = ParseHelper.Parse<decimal>(row["MATCH"]),
                        WHERE_FMT = ParseHelper.Parse<string>(row["WHERE_FMT"]),
                        SUBJ_CLASS_ID = ParseHelper.Parse<string>(row["SUBJ_CLASS_ID"])


                    };
                });
            if (Obj != null)
            {
                return Obj;
            }
            return null;

        }

        /// <summary>
        /// 根据id获取主题分类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ThemeClass getThemeClassById(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from TB_SUBJECT_CLASS where SUBJ_CLASS_ID =" + id + "");

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            ThemeClass Obj = DbManager.GetInfo(cmd,
                delegate(out ThemeClass u, DataRow row)
                {
                    u = new ThemeClass
                    {
                        SUBJ_CLASS_ID = ParseHelper.Parse<string>(row["SUBJ_CLASS_ID"]),
                        SUBJ_CLASS_NAME = ParseHelper.Parse<string>(row["SUBJ_CLASS_NAME"])
                    };
                });
            if (Obj != null)
            {
                return Obj;
            }
            return null;

        }
       

        /// <summary>
        /// 获取主题分类列表
        /// </summary>
        /// <returns></returns>
        public List<ThemeClass> getThemeClass(ThemeClass cond, PageInfo page, OrderInfo order)
        {

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, "sp_getpageThemeClassList",
                   delegate()
                   {
                       return DbCmdHelper.CreateParameters(dbProvider, true).ToArray();
                   });
            cmd.SetParamValues(order, page);
            List<ThemeClass> exttypeList = DbManager.GetPageInfos<ThemeClass>(cmd, page,
                delegate(out ThemeClass dict, DataRow row)
                {
                    dict = new ThemeClass
                    {
                        SUBJ_CLASS_ID = ParseHelper.Parse<string>(row["SUBJ_CLASS_ID"]),
                        SUBJ_CLASS_NAME = ParseHelper.Parse<string>(row["SUBJ_CLASS_NAME"])

                    };
                });
            return exttypeList;
        }

        /// <summary>
        /// 根据名称获取主题
        /// </summary>
        /// <param name="extent"></param>
        /// <returns></returns>
        public Tb_SubjectInfo getSubjectByType(string subjectName)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from tb_subject where SUBJ_NAME ='" + subjectName + "'");

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            Tb_SubjectInfo Obj = DbManager.GetInfo(cmd,
                delegate(out Tb_SubjectInfo u, DataRow row)
                {
                    u = new Tb_SubjectInfo
                    {
                        SUBJ_ID = ParseHelper.Parse<string>(row["SUBJ_ID"]),
                        SUBJ_NAME = ParseHelper.Parse<string>(row["SUBJ_NAME"]),
                        KEYW = ParseHelper.Parse<string>(row["KEYW"]),
                        EXT_CONT = ParseHelper.Parse<string>(row["EXT_CONT"]),
                        PRORITY = ParseHelper.Parse<decimal>(row["PRORITY"]),
                        MATCH = ParseHelper.Parse<decimal>(row["MATCH"]),
                        WHERE_FMT = ParseHelper.Parse<string>(row["WHERE_FMT"])


                    };
                });
            if (Obj != null)
            {
                return Obj;
            }
            return null;

        }
        /// <summary>
        /// 根据主题类别id获取对应主题列表
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public DataSet QueryThemeByThemeTypeId(string themeTypeId)
        {
            DataSet ds = new DataSet();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUBJ_ID,SUBJ_NAME,PRORITY from tb_subject");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable dt = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            dt.TableName = "dt";
            strSql.Clear();
            strSql.Append("select SUBJ_CLASS_ID,SUBJ_ID from tb_themetype_theme where SUBJ_CLASS_ID= '" + themeTypeId + "' order by SUBJ_ID");
            cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable dt1 = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            dt1.TableName = "dt1";
            ds.Tables.Add(dt.Copy());
            ds.Tables.Add(dt1.Copy());
            return ds;
        }

        /// <summary>
        /// 根据用户id获取对应主题类别列表
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        public DataSet QueryThemeTypeByUser(string userId)
        {
            DataSet ds = new DataSet();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUBJ_CLASS_ID,SUBJ_CLASS_NAME from tb_subject_class");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable dt = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            dt.TableName = "dt";
            strSql.Clear();
            strSql.Append("select USER_ID,SUBJ_CLASS_ID from tb_user_theme_type where USER_ID= '" + userId + "'");
            cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DataTable dt1 = DbHelper.ExecFullDataTable(cmd.DbProvider, cmd.DbCmdInfo);
            dt1.TableName = "dt1";
            ds.Tables.Add(dt.Copy());
            ds.Tables.Add(dt1.Copy());
            return ds;
        }
        /// <summary>
        /// 更新主题
        /// </summary>
        /// <param name="cond"></param>
        /// <returns></returns>
        public bool UpdateSubject(Tb_SubjectInfo cond)
        {
            StringBuilder sbSql = new StringBuilder();
            List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
            sbSql.Append(@"update TB_SUBJECT set SUBJ_NAME='" + cond.SUBJ_NAME + "',KEYW='" + cond.KEYW + "',EXT_CONT='" + cond.EXT_CONT + "',PRORITY='" + cond.PRORITY + "',MATCH='" + cond.MATCH + "',WHERE_FMT='" + cond.WHERE_FMT + "' WHERE SUBJ_ID='" + cond.SUBJ_ID + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmdlist.Add(cmd.DbCmdInfo);
            
            return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());
        }

        /// <summary>
        /// 新增主题
        /// </summary>
        /// <param name="cond"></param>
        /// <returns></returns>
        public bool themeAdd(Tb_SubjectInfo cond)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" insert into TB_SUBJECT(SUBJ_NAME, KEYW, EXT_CONT,PRORITY,MATCH,WHERE_FMT)
                           values(:p_SUBJ_NAME, :p_KEYW, :p_EXT_CONT,:p_PRORITY,:p_MATCH,:p_WHERE_FMT)");


            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString(),
                delegate
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_NAME", "KEYW", "EXT_CONT", "PRORITY", "MATCH", "WHERE_FMT").ToArray();
                });

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmd.SetParamValues(cond.SUBJ_NAME, cond.KEYW, cond.EXT_CONT, cond.PRORITY, cond.MATCH, cond.WHERE_FMT);
            return DbManager.ExecNonQuery(cmd) > 0;
        }


        //删除主题
        public bool deleteSub(Tb_SubjectInfo cond)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();

                List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
                strSql.Append("delete TB_SUBJECT WHERE SUBJ_ID=:p_SUBJ_ID");
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString(),
                    delegate()
                    {
                        return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_ID").ToArray();
                    });
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                cmd.SetParamValues(cond.SUBJ_ID);
                cmdlist.Add(cmd.DbCmdInfo);

                DbCmdHelper cmd2 = new DbCmdHelper(dbProvider, "delete TB_THEMETYPE_THEME where SUBJ_ID=:p_SUBJ_ID",
                    delegate()
                    {
                        return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_ID").ToArray();
                    });
                cmd2.DbCmdInfo.CommandType = CommandType.Text;
                cmd2.SetParamValues(cond.SUBJ_ID);
                cmdlist.Add(cmd2.DbCmdInfo);
                return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());
            }
            catch (Exception ex)
            {
                return false;
            }
            
            
        }

        /// <summary>
        /// 根据名称获取主题类别
        /// </summary>
        /// <param name="extent"></param>
        /// <returns></returns>
        public Tb_SubjectInfo getThemeTypeByName(string Name)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from tb_subject_class where SUBJ_CLASS_NAME ='" + Name + "'");

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString());
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            Tb_SubjectInfo Obj = DbManager.GetInfo(cmd,
                delegate(out Tb_SubjectInfo u, DataRow row)
                {
                    u = new Tb_SubjectInfo
                    {
                        SUBJ_CLASS_ID = ParseHelper.Parse<string>(row["SUBJ_CLASS_ID"]),
                        SUBJ_CLASS_NAME = ParseHelper.Parse<string>(row["SUBJ_CLASS_NAME"])
                    };
                });
            if (Obj != null)
            {
                return Obj;
            }
            return null;

        }
        /// <summary>
        /// 更新主题类别
        /// </summary>
        /// <param name="cond"></param>
        /// <returns></returns>
        public bool UpdateThemeType(ThemeClass cond)
        {
            StringBuilder sbSql = new StringBuilder();
            List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
            sbSql.Append(@"update tb_subject_class set SUBJ_CLASS_NAME='" + cond.SUBJ_CLASS_NAME + "'  WHERE SUBJ_CLASS_ID='" + cond.SUBJ_CLASS_ID + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmdlist.Add(cmd.DbCmdInfo);

            return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());
        }

        /// <summary>
        /// 新增主题类别
        /// </summary>
        /// <param name="cond"></param>
        /// <returns></returns>
        public bool themeTypeAdd(ThemeClass cond)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" insert into tb_subject_class(SUBJ_CLASS_NAME)
                           values(:p_SUBJ_CLASS_NAME)");


            DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString(),
                delegate
                {
                    return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_CLASS_NAME").ToArray();
                });

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmd.SetParamValues(cond.SUBJ_CLASS_NAME);
            return DbManager.ExecNonQuery(cmd) > 0;
        }

        //删除主题类别
        public bool deleteThemeType(ThemeClass cond)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();

                List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
                strSql.Append("delete tb_subject_class WHERE SUBJ_CLASS_ID=:p_SUBJ_CLASS_ID");
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql.ToString(),
                    delegate()
                    {
                        return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_CLASS_ID").ToArray();
                    });
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                cmd.SetParamValues(cond.SUBJ_CLASS_ID);
                cmdlist.Add(cmd.DbCmdInfo);

                DbCmdHelper cmd1 = new DbCmdHelper(dbProvider, "delete TB_THEMETYPE_THEME where SUBJ_CLASS_ID=:p_SUBJ_CLASS_ID",
                    delegate()
                    {
                        return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_CLASS_ID").ToArray();
                    });
                cmd1.DbCmdInfo.CommandType = CommandType.Text;
                cmd1.SetParamValues(cond.SUBJ_CLASS_ID);
                cmdlist.Add(cmd1.DbCmdInfo);

                DbCmdHelper cmd2 = new DbCmdHelper(dbProvider, "delete tb_user_theme_type where SUBJ_CLASS_ID=:p_SUBJ_CLASS_ID",
                    delegate()
                    {
                        return DbCmdHelper.CreateParameters(dbProvider, "SUBJ_CLASS_ID").ToArray();
                    });
                cmd2.DbCmdInfo.CommandType = CommandType.Text;
                cmd2.SetParamValues(cond.SUBJ_CLASS_ID);
                cmdlist.Add(cmd2.DbCmdInfo);
                return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());
            }
            catch (Exception ex)
            {
                return false;
            }

        }


        /// <summary>
        /// 删除当前用户主题
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public bool deleteUserThemeTypeByUserId(string paras)
        {
            string[] paraAtt = paras.Split(',');
            string userid = paraAtt[0];
            paras = paras.Substring(paras.IndexOf(",")+1);

            StringBuilder sbSql = new StringBuilder();
            List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
            sbSql.Append(@"delete  from tb_user_theme_type where  user_id='" + userid + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmdlist.Add(cmd.DbCmdInfo);
            return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());
           
        }

        /// <summary>
        /// 删除当前主题类别-主题
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public bool deleteThemeByThemeId(string paras)
        {
            string[] paraAtt = paras.Split(',');
            string themetypeid = paraAtt[0];
            paras = paras.Substring(paras.IndexOf(",") + 1);

            StringBuilder sbSql = new StringBuilder();
            List<DbCmdInfo> cmdlist = new List<DbCmdInfo>();
            sbSql.Append(@"delete  from tb_themetype_theme where  SUBJ_CLASS_ID='" + themetypeid + "'");
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sbSql.ToString());

            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmdlist.Add(cmd.DbCmdInfo);
            return DbManager.ExecTrans(dbProvider, cmdlist.ToArray());

        }

        /// <summary>
        /// 添加用户主题类别
        /// </summary>
        /// <param name="teamid"></param>
        /// <param name="users"></param>
        /// <returns></returns>
        public bool AddUserThemeTypeInfo(string userid, string themetypeid)
        {

            try
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into  tb_user_theme_type(USER_ID,SUBJ_CLASS_ID) values ('" + userid + "','" + themetypeid + "')");
                DbCmdHelper cmd1 = new DbCmdHelper(dbProvider, strSql.ToString());

                cmd1.DbCmdInfo.CommandType = CommandType.Text;
                return DbManager.ExecTrans(cmd1.DbProvider, cmd1.DbCmdInfo);

            }
            catch (Exception e)
            {
                return false;
            }


        }

        /// <summary>
        /// 添加主题类别-主题
        /// </summary>
        /// <param name="teamid"></param>
        /// <param name="users"></param>
        /// <returns></returns>
        public bool AddThemeType_ThemeInfo(string themetypeid, string themeid)
        {

            try
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into  tb_themetype_theme(SUBJ_ID,SUBJ_CLASS_ID) values ('" + themeid + "','" + themetypeid + "')");
                DbCmdHelper cmd1 = new DbCmdHelper(dbProvider, strSql.ToString());

                cmd1.DbCmdInfo.CommandType = CommandType.Text;
                return DbManager.ExecTrans(cmd1.DbProvider, cmd1.DbCmdInfo);

            }
            catch (Exception e)
            {
                return false;
            }


        }
    }
}
